import flask
import sqlite3
import json

app = flask.Flask(__name__)


# 学生考勤
class StudentDB:
    def openDB(self):
        self.con = sqlite3.connect("students0404.db")
        self.cursor = self.con.cursor()

    def closeDB(self):
        self.con.commit()
        self.con.close()

    def initTable(self):
        res = {}
        try:
            self.cursor.execute(
                "create table students (No varchar(16) primary key,Name varchar(16), Sex varchar(8), Age int , IP  varchar(20))")
            res["msg"] = "OK"
        except Exception as err:
            res["msg"] = str(err)
        return res

    def insertRow(self, No, Name, Sex, Age, IP):
        res = {}
        try:
            self.cursor.execute("insert into students (No,Name,Sex,Age,IP) values (?,?,?,?,?)",
                                (No, Name, Sex, Age, IP))
            res["msg"] = "OK"
        except Exception as err:
            res["msg"] = str(err)
        return res

    def deleteRow(self, No):
        res = {}
        try:
            self.cursor.execute("delete from students where No=?", (No,))
            res["msg"] = "OK"
        except Exception as err:
            res["msg"] = str(err)
        return res

    def selectRows(self):
        res = {}
        try:
            data = []
            self.cursor.execute("select * from students order by No")
            rows = self.cursor.fetchall()
            for row in rows:
                d = {}
                d["No"] = row[0]
                d["Name"] = row[1]
                d["Sex"] = row[2]
                d["Age"] = row[3]
                d["IP"] = row[4]
                data.append(d)
            res["msg"] = "OK"
            res["data"] = data
        except Exception as err:
            res["msg"] = str(err)
        return res

# 签到页面的接口地址
@app.route("/signAddUI")
def signAddUI():
    fobj = open("sign/signAdd.html", "rb")
    data = fobj.read()
    fobj.close()
    return data

# 签到页面的接口地址
@app.route("/")
def index():
    fobj = open("sign/list.html", "rb")
    data = fobj.read()
    fobj.close()
    return data

# 查看所有考勤數據
@app.route("/list")
def list():
    res = {}
    db = StudentDB()
    db.openDB();
    res = db.selectRows()
    db.closeDB()
    return json.dumps(res)

# 初始化数据库
@app.route("/init")
def init():
    res = {}
    db = StudentDB()
    db.openDB()
    res = db.initTable()
    db.closeDB()
    return json.dumps(res)


# 签到数据提交的接口
@app.route("/signAdd", methods=["GET", "POST"])
def signAdd():
    res = {}
    db = StudentDB()
    db.openDB()
    IP = flask.request.remote_addr
    No = flask.request.values.get("No") if "No" in flask.request.values else ""
    Name = flask.request.values.get("Name") if "Name" in flask.request.values else ""
    Sex = flask.request.values.get("Sex") if "Sex" in flask.request.values else ""
    Age = flask.request.values.get("Age") if "Age" in flask.request.values else ""
    res = db.insertRow(No, Name, Sex, Age, IP)
    db.closeDB()
    return json.dumps(res)


if __name__ == "__main__":
    app.run(host="0.0.0.0")
